import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.sql.*;

@WebServlet(urlPatterns = {"/h2jdbc"})
public class testJDBCServlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //http://localhost:8080/test/testJdbc.html


        response.setContentType("text/html;charset=UTF-8");
        java.sql.Connection conn = null;
        java.lang.String strConn;
        java.sql.Statement sqlStmt = null; //语句对象
        java.sql.ResultSet sqlRst = null; //结果集对象
        try {
            conn = java.sql.DriverManager.getConnection("jdbc:h2:~/test_customer;MODE=MYSQL;DB_CLOSE_DELAY=-1", "sa", "");

            sqlStmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);

            CREATE TABLE `student` (
                `studentid` int(11) unsigned NOT NULL AUTO_INCREMENT,
                `studentno` varchar(100) NOT NULL,
                `course` varchar(100) NOT NULL,
                `score` int(10) NOT NULL
            );

            String sqlQuery = "CREATE TABLE `student` (\n" +
                    "                `studentid` int(11) unsigned NOT NULL AUTO_INCREMENT,\n" +
                    "                `studentno` varchar(100) NOT NULL,\n" +
                    "                `course` varchar(100) NOT NULL,\n" +
                    "                `score` int(10) NOT NULL\n" +
                    "            )";
            sqlStmt.executeUpdate(sqlQuery);

            String[] sql = {
                    "insert into `student` (`studentid`,`studentno`,`course`,`score`) values (NULL,'20174140','语文',85)",
                    "insert into `student` (`studentid`,`studentno`,`course`,`score`) values (NULL,'20174141','JavaEE',95)",
                    "insert into `student` (`studentid`,`studentno`,`course`,`score`) values (NULL,'20174142','PHP',75)"
            };
            for(int i=0;i<sql.length;i++){
                sqlStmt.executeUpdate(sql[i]);
            }
            String sqlQuery = "select * from student";
            sqlRst = sqlStmt.executeQuery(sqlQuery);

            while (sqlRst.next()) {
                String id = sqlRst.getString("studentid");
                String no = sqlRst.getString("studentno");
                String cour = new String(sqlRst.getString("course").getBytes(StandardCharsets.UTF_8));
                String sum = sqlRst.getString("score");
                String res = "<p>id:"+id+" 学号："+no+" 课程："+cour+" 成绩："+sum+"<p/>";
                response.getWriter().println(res);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }


    }
}
